package com.xqgl.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.xqgl.bean.Pet;
import com.xqgl.dao.PetDao;
import com.xqgl.util.ConnertionUtil;

public class PetDaoImp implements PetDao{
	Connection conn = ConnertionUtil.getConnection();
	PreparedStatement ps;
	Statement statement;
	
	public ArrayList<Pet> getAllUserInfos() {
		ArrayList<Pet> pets = new ArrayList<>();
		String sql = "select * from pet ";
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rst=ps.executeQuery(sql);
			rst.last();
			rst.beforeFirst();
			while(rst.next()) {
				Pet pet = new Pet();
				pet.setId(rst.getInt("id"));
				pet.setPetpicture(rst.getString("petpicture"));
				pet.setUsername(rst.getString("username"));
				pet.setPetname(rst.getString("petname"));
				pet.setPetcolour(rst.getString("petcolour"));
				pet.setPetremark(rst.getString("petremark"));
				pet.setSytime(rst.getString("sytime"));
				pet.setPetctime(rst.getString("petctime"));
				pets.add(pet);
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return pets;
	}
	
	@Override
	public int add(Pet pet) throws SQLException {
		String sql = "insert into pet(petpicture,username,petname,petcolour,petremark,sytime,petctime)"
				+ "values(?,?,?,?,?,?,?)";
		ps = conn.prepareStatement(sql);
		ps.setString(1, pet.getPetpicture());
		ps.setString(2, pet.getUsername());
		ps.setString(3, pet.getPetname());
		ps.setString(4, pet.getPetcolour());
		ps.setString(5, pet.getPetremark());
		ps.setString(6, pet.getSytime());
		ps.setString(7, pet.getPetctime());
		int a = ps.executeUpdate();
		ps.close();
		return a;
	}

	@Override
	public int update(Pet pet) throws SQLException {
		String sql = "update pet set petpicture = '"+pet.getPetpicture()+"', username = '"+pet.getUsername()+"', petname = '"+pet.getPetname()+"',"
				+ "petcolour = '"+pet.getPetcolour()+"', petremark = '"+pet.getPetremark()+"', sytime = '"+pet.getSytime()+"', petctime = '"+pet.getPetctime()+"' where id = "+pet.getId()+"";
		ps = conn.prepareStatement(sql);
		int b = ps.executeUpdate();
		ps.close();
		return b;
	}

	@Override
	public boolean delete(int id) throws SQLException {
		String sql = "delete from pet where id = "+id+"";
		statement = conn.createStatement();
		boolean c = statement.execute(sql);
		statement.close();
		return !c;
	}

	@Override
	public Pet findById(int id) throws SQLException {
		Pet pet = new Pet();
		String sql = "select * from pet where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet set = ps.executeQuery();
			while(set.next()){
				pet.setId(set.getInt("id"));
				pet.setPetpicture(set.getString("petpicture"));
				pet.setUsername(set.getString("username"));
				pet.setPetname(set.getString("petname"));
				pet.setPetcolour(set.getString("petcolour"));
				pet.setPetremark(set.getString("petremark"));
				pet.setSytime(set.getString("sytime"));
				pet.setPetctime(set.getString("petctime"));
			}
			ps.close();
			return pet;
	}

}
